View Course Outline

The SQL Server 2000 Implementing Database Design training course will teach you how to design and implement a powerful, functional database with Microsoft SQL Server 2000. Expert instructor Wayne Snyder’s friendly presentation style makes this complex technical subject matter easy to understand. At the conclusion of this course you will understand the core techniques of implementing database design and will be prepared to pass exam #70-229 which provides core credit toward Microsoft Certified Database Administrator certification, elective credit toward Microsoft Certified Systems Engineer certification, and elective credit toward Microsoft Certified Solution Developer certification.

Entire course includes 14+ hours of total training time...

• 8 CDs
• Over 14 hours of media run time

SQL Server 2000 Implementing
Database Design

The SQL Server 2000 Implementing Database Design training course will teach you how to design and implement a powerful, functional database with Microsoft SQL Server 2000. Expert instructor Wayne Snyder’s friendly presentation style makes this complex technical subject matter easy to understand. At the conclusion of this course you will understand the core techniques of implementing database design and will be prepared to pass exam #70-229 which provides core credit toward Microsoft Certified Database Administrator certification, elective credit toward Microsoft Certified Systems Engineer certification, and elective credit toward Microsoft Certified Solution Developer certification.

SESSION 1
Total Time: 112 Minutes

Section A: Introduction

• Overview

Section B: Normalization

• Basic Concept
• Redundancy
• Design Language
• Entity
• First Normal Form
• Primary Keys
• Second Normal Form
• Third Normal Form
• Relationships
• Business Rules
• Normalization Benefits

Section C: Creating a Database

• Overview
• Database Objects
• Database Creation Process
• Transaction Log
• Create Option
• Create Syntax
• Query Analyzer
• Collate Order
• Enterprise Manager
• Recovery Considerations

Section D: Placing Database Files

• Storage Management
• Hardware RAID
• Filegroups
• Files & Filegroups
• Filegroups Maintenance
• Performance Considerations

Section E: Maintaining Databases

• Growth
• Alter Database
• Database Options
• Shrink Database
• Exam Database
• Space Estimation
• Transaction Log Size
• Rules of Thumb
• Log File Issues
• Extensive Log Activity

Section F: Basic SQL Data Types

• Create Table
• Standard Data Types
• Exact Numerics
• Integer Variables
• Approximate Numerics
• Character
• Variable vs. Fixed
• Unicode

SESSION 2
Total Time: 111 Minutes

Section A: Advanced SQL Data Types

• Date & Time
• Date Format
• Money
• Invalid Entry
• Binary
• GUID
• BLOB
• Special Data Types

Section B: Creating a Table

• Simply Syntax
• Null or Not Null
• Space Allocation
• Temporary Tables
• Primary Key Values
• Identity Column
• Using Identity Values
• Scope Identity
• Unique Identifier
• Newid Function
• Extended Properties
• Altering & Dropping Tables

Section C: Data Integrity Concepts

• Types
• Data Integrity
• Entity Integrity
• Domain Integrity
• Referential Integrity

Section D: Data Integrity Implementation

• Enforcement
• Constraints
• Generic Constraints
• Defaults
• Check Constraints
• Table vs. Column Constraint
• Column Constraint
• Alter Constraints
• Constraint Options

Section E: Primary Key Constraint

• Overview
• Implement Primary Key Constraint
• Duplicate Key Values
• Multi-Column Key
• Unique Constraint
• Add Unique Constraint

Section F: Foreign Key Constraint

• Overview
• Foreign Key Rules
• Cascade Update

SESSION 3
Total Time: 107 Minutes

Section A: Select Statement

• Basic Select
• Special Identifiers
• 4-Part Naming Convention
• Select Order
• Where Clauses
• Comparison Operators
• Range of Values
• Values in a List
• String Pattern Matching
• String Comparison Operator
• Like Operator
• Not Like Operator
• Null Checking
• Logical Operator
• Not, And, Or Operators
• Dynamic SQL

Section B: Formatting Result Sets

• Sorting
• Order By
• Eliminating Duplicates
• Order By with Distinct
• Column Alias
• Using Literals

Section C: Summarizing Data

• Aggregate Functions
• Aggregate Examples
• Count Distinct
• Group By
• Group By Having
• Having with Aggregates
• Rollups
• Cube

Section D: Functions & Set Options

• System Functions
• Set Options
• Scalar Functions
• Object Properties
• Convert Function
• String Functions
• GetDate Function
• Date & Time Functions
• Row Count Set Option

Section E: Inner Joins

• Joins
• GUI Joins
• Inner Join
• Alias Table Names
• Join Result Options
• Multiple Table Joins

SESSION 4
Total Time: 104 Minutes

Section A: Outer Joins, Cross Joins & Unions

• Outer Joins
• Left & Right Outer Join
• Cross Join
• Self-Referencing Table
• Self-Reference Outer Join
• Denormalizing
• Unions

Section B: Subqueries

• Introduction
• Rules
• Nested Subquery
• Multiple Value Nested Subqueries
• Distinct Subqueries
• Correlated Subqueries
• Complicated Subqueries

Section C: Insert/Delete/Update

• Insert Statement
• Defaults
• Insert Select
• Truncate Table
• Deleting Records
• Updating Records
• Update with Join

Section D: Indexing

• Introduction
• Heap vs. Clustered
• Data Access
• Clustered Index
• Why Clustered Indexes?
• Non-Clustered Indexes
• Why Non-Clustered Indexes?

Section E: Indexes & Fillfactor

• Creating Indexes
• Computed Columns
• Maintenance Issues
• Full Pages
• Fillfactor
• Execution Plan with Index
• Aggregate with Index
• Clustered Index

Section F: Indexes & Fragmentation

• External Fragmentation
• Data Fragmentation
• Scan Density
• DBCC Index Defrag
• Dropping Index
• Index Hints

SESSION 5
Total Time: 105 Minutes

Section A: Index Statistics

• Overview
• Density
• Index Statistics
• Index Statistics Usage
• Updating Statistics
• View Statistics
• Set Statistic Properties
• Using Query Plan
• Update Statistics
• Force Index Usage
• Use Updated Statistics
• Update Statistic Commands
• Show Sysindexes
• Turn On Statistic

Section B: Query Optimization

• Slow Queries
• Query Plan
• Dual Indexes Usage
• And Operators
• Or Operators
• Aggregates
• Joins
• Merge Join
• Entity Relationship Diagram
• Verify Cost
• Force Index
• Search Arguments
• Like Clause

Section C: SQL Profiler

• Overview
• Event Classes
• Run Profiler
• Tracefile Properties
• Index Tuning Wizard
• Analysis
• Trace Replay
• Access Query Analyzer
• Overview
• Set Processor Usage
• Query Governor

Section D: Views

• Overview
• View Details
• Create View
• Using Views
• Updateable Views
• Alter View
• Insert Record
• Check Options
• Update & Delete Records

Section E: Linked Servers

• Overview
• Query Types
• Setting up the Link
• Login
• Options
• Open Query
• RPC’s
• Create Linked Server (Excel)
• Distributed Passthrough Queries
• RPC/Adhoc
• Union Query

SESSION 6
Total Time: 110 Minutes

Section A: Indexed & Distributed Views

• Indexed View Usage
• Scheme Bound View
• Indexed View
• Data Partitioning
• Partition View
• Create Data Partition
• Create Data Partition View
• Setting Up
• Modify Users View
• Insert, Update & Delete
• View Issues

Section B: Programming SQL

• Global Functions
• Local Variables
• Define a Local Variable
• Define Multi-Local Variables
• Assignment Select Statement
• Variable Value Assignment
• Control of Flow
• Multiple Statement
• While Loop
• Until/Break
• GoTo/WaitFor

Section C: Other Language Elements

• Simple Case Statements
• Searched Case
• Raiserror
• View Logs
• User Messages
• Validate Data
• XPLOG Events
• Commenting

Section D: Transactions

• Overview
• Rollback
• Transaction Examples
• Partial Rollback
• Nested Transactions
• Avoid Nested
• Implicit
• Implicit Starters
• Error Checking
• Set Implicit On
• Restricted & Best Practices
• Linked Server & Remote Procedure
• Distributed Transaction

Section E: Locks & Lock Types

• Lost & Erroneous Transactions
• Non-Repeatable & Phantoms
• Benefits & Usage
• Lock Types
• Lock Process
• Intent Locks & Bulk Update
• Isolation Levels
• Locked Record Trace
• Set Lock Time Out

SESSION 7
Total Time: 107 Minutes

Section A: Lock Manipulation

• Lock Hints
• Using Locks
• Deadlock Error
• Avoiding & Handling Deadlocks
• Locking Best Practices

Section B: Cursors

• Overview
• Cursor Types & Behavior
• Cursor Steps
• Modifying Data
• Using Cursor
• Update Data
• Cursor Best Practices

Section C: Stored Procedures

• Overview
• Create & Execute
• Using Stored Procedures
• Parameters
• Using Parameters
• Local Variables
• Default Parameter Values
• Output Parameters
• Error Checking
• Testing Stored Procedures
• Return Status
• Plan Sharing & Best Practices

Section D: User Defined Functions

• Function Types
• Calling Functions
• Schema Binding
• Determinism
• Function Syntax
• Calling Scalar Functions
• Using Functions
• Table Value Functions
• Calling Table Functions
• Multi-Statement Table
• Best Practices

Section E: After Triggers

• Overview
• Plan Trigger Use
• Trigger Functionality
• Inserted, Updated, Deleted Tables
• Using Triggers
• Testing Triggers
• Cascading Updates

SESSION 8
Total Time: 98 Minutes

Section A: Instead-Of Triggers

• Overview
• Using Instead-Of Triggers
• Create Trigger
• Nested Triggers
• Instead-Of vs. After

Section B: Data Movement

• Overview
• DTS Tools
• DTS Packages
• DTS Data Lineage
• DTS Import/Export Wizard
• Export to Flat File
• Import Data
• DTS Package Workflow

Section C: Replication

• Overview
• Replication Roles
• Replication Types
• Merge
• Create Merge Publication
• Subscriptions
• Updating Subscribers
• New Replication Features

Section D: Permissions & Roles

• Overview
• Object Permissions
• Chain of Ownership
• Permissions & Chain of Ownership
• Row Level Security
• Assign Row Level Security
• Roles
• Understanding Roles
• Application Roles

Section E: XML

• Selecting
• For XML Auto
• For XML Raw
• For XML Explicit
• Open XML
• Additional Resources

back to top

Wayne Snyder is a recognized expert in application development and database design and modeling with specialization in Microsoft SQL Server. Wayne has worked with SQL from its first release, and won the prized Microsoft MVP (Most Valuable Professional) award in 1998. His certifications include MCDBA, MCSE, and MCT. Wayne is a highly respected speaker at industry conferences and a sought-after reviewer for major technical publications. He is a popular instructor and consultant currently working with IKON Education Services.

back to top